package com.durablemethods.tsmserver.home;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.durablemethods.tsmserver.model.TraderTradeRevenue;
import com.google.appengine.api.rdbms.AppEngineDriver;

public class TradeRevenueHome {
	public List<TraderTradeRevenue> getTradeRevenues(){
		List<TraderTradeRevenue> returnRevenues = new ArrayList<TraderTradeRevenue>();
		Connection c = null;
	    
	      try {
	    	//printWriter.println("Register Driver - pre");
			DriverManager.registerDriver(new AppEngineDriver());
			//printWriter.println("Register Driver -- done -- getting connections");
			c = DriverManager.getConnection("jdbc:google:rdbms://durablemethods.com:duravest:tsm","jamie","catal0g");
			//printWriter.println("We have a connection");
			Statement s = c.createStatement();
			//printWriter.println("Executing Query -- pre");
			ResultSet rs = s.executeQuery(getQuery());
			while (rs.next()){
				TraderTradeRevenue returnSummary = new TraderTradeRevenue();
				returnSummary.setTrader(rs.getString("Trader"));
				returnSummary.setRevenue(rs.getDouble("Total"));
				returnSummary.setTransactions(rs.getInt("TRANSACTIONS"));
				returnRevenues.add(returnSummary);
			}
			c.close();
		} catch (SQLException e) {
			//printWriter.println("Error found");
			//printWriter.println(e.getMessage());
			e.printStackTrace();
		}
		return returnRevenues;
		
	}
	
	private String getQuery(){
		StringBuilder builder = new StringBuilder();
		
		builder.append(" select t.trader as TRADER, sum(t.subtotal) as TOTAL, count(*) as TRANSACTIONS from (");
		builder.append(" select trans.id as TRANS_ID, traders.id as TRADER_ID,teams.name as Team, traders.NAME as Trader, trans.TYPE as IPO_OR_TRADE, trans.quantity as Quantity, trans.PRICE, trans.quantity*trans.PRICE*-1 as subtotal");
		builder.append(" from TEM_SPORTS.TRADES trans, TEM_SPORTS.TEAMS teams, TEM_SPORTS.TRADERS traders");
		builder.append(" where trans.team_id = teams.id");
		builder.append(" and trans.trader_id = traders.id");
		builder.append(" and traders.id!=1");
		builder.append(" order by Team, Trader");
		builder.append(" ) t");
		builder.append(" group by t.trader"); 
		builder.append(" order by TOTAL desc ");   
		return builder.toString();
	}
	public static void main(String[] args) {
		TradeRevenueHome home = new TradeRevenueHome();
		System.out.println(home.getQuery());
	}

}
